#coding=utf-8
from pymysql import connect
import traceback
from config import config

DB_HOST = config.DB_HOST
DB_USER = config.DB_USER
DB_PASSWD = config.DB_PASSWD
DB_NAME = config.DB_NAME
DB_PORT = config.DB_PORT
DB_CHARSET = config.DB_CHARSET

TABLE_COLUMNS_SQL = 'show columns from %s'

class PDB:

	idle = []
	use = []
	index = 100

	def __init__(self):
		pass
		# self.addconnect()

	def getconnect(self):
		x = 0
		i = ''
		while x <= 3:
			x += 1
			try:
				i = self.idle.pop()
				i['conn'].ping()
				i['index'] += 1
				if i['index'] > self.index:
					i['cur'].close()
					i['index'] = 0
					i['cur'] = i['conn'].cursor()
				self.use.append(i)
				break
			except Exception, e:
				print e
				self.addconnect()
				# print e
				continue
		return i

	def addconnect(self):
		conn = connect(host=DB_HOST, user=DB_USER
							, passwd=DB_PASSWD, db=DB_NAME, port=DB_PORT, charset=DB_CHARSET)
		cur = conn.cursor()
		self.idle.append({'conn':conn, 'cur':cur, 'index':0})

	def breakconnect(self, x):
		if x in self.use:
			self.use.remove(x)
			self.idle.append(x)
		# print 'euse:', len(self.use)
		# print 'eidle', len(self.idle)

class BaseDb():
	x = None
	conn = None
	cur = None
	def __init__(self):
		self.cont()

	def cont(self):
		if self.x is None or self.conn is None or self.cur is None:
			# self.x = 1
			# self.conn = connect(host=conf.host, port=3306, user=conf.user, passwd=conf.passwd, db=conf.database,charset='utf8')
			# self.cur = self.conn.cursor()
			self.x = pdb.getconnect()
			self.conn = self.x['conn']
			self.cur = self.x['cur']

	def execute(self, query, params=None):
		# yield self.cont()

		count = self.cur.execute(query, params)
		rt_arr = []
		# yield conn.autocommit(True)
		rrr = self.cur.fetchall()
		for row in rrr:
		   rt_arr.append(row)
		# for row in cur.fetchall():
		#     print row
		# self.cur.close()
		# self.conn.close()
		# try:
		#     cur = yield ex_pool(self.db).execute(query, params)
		# except Exception, e:
		#     raise e
		# log.log_sql(query.strip(), params)
		# raise gen.Return(rt_arr)
		return rt_arr

	def executejson(self, query, params=None):
		# yield self.cont()

		count = self.cur.execute(query, params)

		rt_arr = self.getdata(self.cur)
		# rt_arr = self.getdata(self.cur)
		# for row in cur.fetchall():
		#     print row
		# self.cur.close()
		# self.conn.close()
		# try:
		#     cur = yield ex_pool(self.db).execute(query, params)
		# except Exception, e:
		#     raise e
		# log.log_sql(query.strip(), params)
		# raise gen.Return(rt_arr)
		return rt_arr

	def getdata(self, cur):
		results = []

		feild = cur.description
		data = cur.fetchone()
		while data:
			res = {}
			n = 0
			for i in feild:
				if type(data[n]) == unicode:
					res[i[0]] = data[n]
				elif data[n] == None:
					res[i[0]] = ''
				else:
					res[i[0]] = str(data[n])
				n += 1
			results.append(res)
			data = cur.fetchone()

		return results

	def insertmanay(self, table, params):
		# yield self.cont()

		if type(params) != list:
			return '-1'
		elif type(params[0]) != dict:
			return '-1'
		sql = 'INSERT INTO %s %s VALUES %s'
		data = []

		keys = "(" + ",".join(params[0].keys()) + ")"
		feild = '(' + ('%s,'*len(params[0]))[0:-1] +')'
		sql = sql % (table, keys, feild)

		for i in params:
			x = []
			for j in params[0].keys():
				x.append(i[j])
			data.append(tuple(x))

		count = self.cur.executemany(sql, tuple(data))

		# self.conn.autocommit(True)
		# self.cur.close()
		# self.conn.close()
		# raise gen.Return(count)
		return count

	def insert(self, table, params):
		# yield self.cont()

		sql = 'INSERT INTO %s %s VALUES %s'
		queryid = 'SELECT LAST_INSERT_ID()'
		keys = "(" + ",".join(params.keys()) + ")"
		values = "('" + "','".join(params.values()) + "')"
		# print params.values()
		# print params.values()
		# a = [i.encode('utf-8') for i in params.values()]
		# print a
		# print str(values).('u')
		sql = sql % (table, keys, values)
		# print sql
		self.cur.execute(sql)
		self.cur.execute(queryid)
		count = self.cur.fetchall()
		# print count[0][0]
		# self.conn.autocommit(True)
		# self.cur.close()
		# self.conn.close()
		# raise gen.Return(count[0][0])
		return count[0][0]

	def commit(self):
		self.conn.commit()

	def close(self):
		if self.x is not None or self.conn is not None or self.cur is not None:
			# self.conn.commit()
			# self.cur.close()
			# self.conn.close()
			# return
			try:
				self.conn.commit()
			except Exception, e:
				print e
			self.cur = None
			self.conn = None
			return pdb.breakconnect(self.x)
		else:
			return

	def upd_or_ins(self, query, params=None):
		# yield self.cont()
		# conn = yield connect(host=conf.host, port=3306, user=conf.user, passwd=conf.passwd, db=self.db,charset='utf8')
		# cur = conn.cursor()
		count = self.cur.execute(query, params)
		# self.conn.autocommit(True)
		# self.cur.close()
		# self.conn.close()
		# raise gen.Return(count)
		return count


pdb = PDB()

if __name__=='__main__':
	db = BaseDb()
	sql = 'select id,productid,pname,vprice,oprice from tp_product_cart_list limit 1,5'
	results = db.executejson(sql)
	for i in results:
		print i['id']
	sql = 'select id,price,orderid from tp_product_cart limit 1,5'
	results = db.executejson(sql)
	for i in results:
		print i['orderid']
	db.close()

'''
UPDATE tp_product_cart_list a 
INNER JOIN tp_product b
ON a.productid = b.id 
SET a.pname = b.name, a.vprice = b.vprice, a.oprice = b.oprice 
where a.pname is NULL 
and a.vprice is NULL
and a.oprice is NULL
'''